﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_PartReceive : Form
    {
        private ComboboxItem orderno;
        private ComboboxItem poid;

        public frm_Add_PartReceive()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            getOrderNoList();
            this.orderNo.Properties.AutoComplete = true;
            getUnitIDList();
            this.unitID.Properties.AutoComplete = true;
        }
        
        ////////////////////////////////////////////////
        /// Load Order No list to form
        ////////////////////////////////////////////////
        private void getOrderNoList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT OrderNo FROM [241_PODetails] WHERE Finished=0 ORDER BY OrderNo";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["OrderNo"].ToString();
                    item.Value = dataReader["OrderNo"].ToString();
                    this.orderNo.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get OrderNo list");
            }
        }

        ////////////////////////////////////////////////
        /// Load PO ID list to form
        ////////////////////////////////////////////////
        private void getPOIDList()
        {
            poID.Properties.Items.Clear();
            if (this.orderNo.SelectedIndex >= 0)
            {
                orderno = (ComboboxItem)orderNo.SelectedItem;
                if (!string.IsNullOrEmpty(orderno.Value.ToString()))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT POID FROM [241_PODetails] WHERE OrderNo='" + orderno.Value.ToString() + "'"
                            + " AND Finished=0 ORDER BY POID";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ComboboxItem item = new ComboboxItem();
                            item.Text = dataReader["POID"].ToString();
                            item.Value = dataReader["POID"].ToString();
                            this.poID.Properties.Items.Add(item);
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get POID list");
                    }
                    if (poID.Properties.Items.Count > 0)
                    {
                        poID.SelectedIndex = 0;
                        this.quantityValue.Enabled = true;
                        this.unitID.Enabled = true;
                        this.arrivalDate.Enabled = true;
                        this.noteText.Enabled = true;
                        this.buttonAdd.Enabled = true;
                    }
                    else
                    {
                        MessageBox.Show("All POID have finished with this OrderNo");
                        this.buttonAdd.Enabled = false;
                    }
                }
            }
        }

        ////////////////////////////////////////////////
        /// Load UnitID list to form
        ////////////////////////////////////////////////
        private void getUnitIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT UnitID FROM [222_Unit] ORDER BY UnitID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["UnitID"].ToString();
                    item.Value = dataReader["UnitID"].ToString();
                    this.unitID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get UnitID list");
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }        
        
        ///////////////////////////
        /// When Click Add Button
        ///////////////////////////        
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            float receivedQuantity = getTotalQuantityArrived();
            float quantityArrival = float.Parse(quantityValue.Text) + receivedQuantity;
            float quantityOrder = getTotalQuantityOrdered();
            if (quantityArrival < quantityOrder)
            {
                ///Insert PartReceive
                insertPartReceive();
                clearForm();
            }
            else
            {
                if (quantityArrival == quantityOrder)
                {
                    ///Insert ParReceive
                    insertPartReceive();

                    ///Update PartReceive to Finished
                    updatePartReceive();

                    ///Update PODetails to Finished
                    updatePODetails();


                    ///Check if PO Finished
                    int count = countNotFinishedOrderNo();
                    if (count == 0)
                    {
                        ///Update PO to Finished
                        updatePO();
                    }
                    clearForm();
                }
                else
                {
                    MessageBox.Show("The selected order only remain " + (quantityOrder - receivedQuantity).ToString());
                }
            }
        }

        ////////////////////////////////////////////////////////
        /// Get total quantity part had been arrived before
        ////////////////////////////////////////////////////////
        private float getTotalQuantityArrived()
        {
            orderno = (ComboboxItem)orderNo.SelectedItem;
            poid = (ComboboxItem)poID.SelectedItem;
            float ret = 0;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SUM(Quantity) FROM [241_PartReceive] GROUP BY POID, OrderNo, Finished HAVING OrderNo='"
                    + orderno.Value.ToString() + "' AND POID='" + poid.Value.ToString() + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ret = float.Parse(dataReader[0].ToString());
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = 0;
            }
            return ret;
        }

        ////////////////////////////////////////////////////////
        /// Get total quantity ordered in PODetails
        ////////////////////////////////////////////////////////
        private float getTotalQuantityOrdered()
        {
            orderno = (ComboboxItem)orderNo.SelectedItem;
            poid = (ComboboxItem)poID.SelectedItem;
            float ret = 0;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SUM(Quantity) FROM [241_PODetails] GROUP BY POID, OrderNo, Finished HAVING OrderNo='"
                    + orderno.Value.ToString() + "' AND POID='" + poid.Value.ToString() + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ret = float.Parse(dataReader[0].ToString());
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = 0;
            }
            return ret;
        }

        /////////////////////////////////////////////////
        /// Get POID compatable with OrderNo
        /////////////////////////////////////////////////
        private void orderNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            getPOIDList();
            checkUnitID();
        }

        ///////////////////////////////////
        /// Check UnitID compatable
        ///////////////////////////////////        
        private void checkUnitID()
        {
            if (this.orderNo.SelectedIndex >= 0)
            {
                orderno = (ComboboxItem)orderNo.SelectedItem;
                poid = (ComboboxItem)poID.SelectedItem;
                string partUnit = null;
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT [222_Part].BuyingUnit AS UnitID FROM [222_Part] WHERE [222_Part].PartNo IN"
                        + " (SELECT [241_PODetails].PartNo COLLATE Japanese_Unicode_CI_AI AS PartNo"
                        + " FROM [241_PODetails] WHERE [241_PODetails].POID='" + poid.Value.ToString() + "'"
                        + " AND [241_PODetails].OrderNo='" + orderno.Value.ToString() + "' AND [241_PODetails].Finished=0)";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader["UnitID"] != DBNull.Value)
                        {
                            partUnit = dataReader["UnitID"].ToString().Trim();
                            if (!string.IsNullOrEmpty(partUnit))
                            {
                                if ((int)partUnit[0] == 65402)
                                {
                                    partUnit = "PC";
                                }
                            }
                        }
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }

                try
                {
                    for (int i = 0; i < this.unitID.Properties.Items.Count; i++)
                    {
                        ComboboxItem item = (ComboboxItem)this.unitID.Properties.Items[i];
                        if (item.Value.ToString().Trim().ToUpper() == partUnit.ToUpper())
                        {
                            this.unitID.SelectedIndex = i;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        //////////////////////////////////////////////////
        /// Insert to PartRecive
        //////////////////////////////////////////////////
        private void insertPartReceive()
        {
            orderno = (ComboboxItem)orderNo.SelectedItem;
            poid = (ComboboxItem)poID.SelectedItem;

            DateTime arrivaldate;
            DateTime.TryParse(arrivalDate.DateTime.Date.ToString(), out arrivaldate);
            try
            {
                myConnection.Open();
                string sql = "INSERT INTO [241_PartReceive] (POID, OrderNo, Quantity, ArrivalDate, Note)"
                    + " VALUES ('" + poid.Value.ToString() + "', '" + orderno.Value.ToString() + "',"
                    + " " + Int32.Parse(quantityValue.Text) + ", @arrivaldate, '" + noteText.Text + "')";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.Parameters.Add("@arrivaldate", SqlDbType.DateTime).Value = arrivaldate.Date;
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                MessageBox.Show("Arrival Part added success.");                
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot add Arrival Part. Please try again.");
            }
        }

        //////////////////////////////////////////////////
        /// Update PartRecive to Finished
        //////////////////////////////////////////////////
        private void updatePartReceive()
        {
            orderno = (ComboboxItem)orderNo.SelectedItem;
            poid = (ComboboxItem)poID.SelectedItem;
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PartReceive] SET Finished=1 WHERE POID='" + poid.Value.ToString() + "'"
                    + " AND OrderNo='" + orderno.Value.ToString() + "' AND Finished=0";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update Arrival Part.");
            }
        }

        //////////////////////////////////////////////////
        /// Update PODetails to Finished
        //////////////////////////////////////////////////
        private void updatePODetails()
        {
            orderno = (ComboboxItem)orderNo.SelectedItem;
            poid = (ComboboxItem)poID.SelectedItem;
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PODetails] SET Finished=1 WHERE POID='" + poid.Value.ToString() + "'"
                    + " AND OrderNo='" + orderno.Value.ToString() + "' AND Finished=0";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update PODetails.");
            }
        }

        //////////////////////////////////////////////////////////////
        /// Count all OrderNo have not finised of POID 
        //////////////////////////////////////////////////////////////
        private int countNotFinishedOrderNo()
        {
            poid = (ComboboxItem)poID.SelectedItem;
            int count = 1;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT COUNT(OrderNo) FROM [241_PODetails]"
                    + " GROUP BY POID, Finished HAVING POID='" + poid.Value.ToString() + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    count = Int32.Parse(dataReader[0].ToString());
                }
                else
                {
                    count = 0;
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                count = 1;
            }
            return count;
        }

        //////////////////////////////////////////////////
        /// Update PO to Finished
        //////////////////////////////////////////////////
        private void updatePO()
        {
            poid = (ComboboxItem)poID.SelectedItem;
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PO] SET Finished=1 WHERE ID='" + poid.Value.ToString() + "'";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                MessageBox.Show("POID " + poid.Value.ToString() + " is now finished");
                this.Dispose();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update PO.");
            }
        }

        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }
    }
}
